package com.baichang.taoshunda.dao.impl;

import com.baichang.framework.dao.impl.BaseDaoImpl;
import com.baichang.framework.model.Page;
import com.baichang.taoshunda.dao.GoodsDao;
import com.baichang.taoshunda.entity.Goods;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Repository
public class GoodsDaoImpl extends BaseDaoImpl<Goods> implements GoodsDao {

    /**
     * 活动下查询商品
     *
     * @param goodsBusId
     * @param type
     * @param goodsName
     * @param nowPage
     * @param pageSize   @return
     */
    @Override
    public Page queryOutGoods(String goodsBusId, Integer type, String goodsName, int nowPage, int pageSize) {
        List list = new ArrayList();
        if (goodsBusId.equals("")) {
            if (type != 2) {
                String sql = "SELECT tp.*,( SELECT COUNT(0) FROM t_goods tg WHERE tg.id=tp.id ),0) AS ischeck FROM t_company tc INNER JOIN t_goods tp ON tc.id=tp.company_id  WHERE tp.status=1 AND tc.is_auth=1 AND tc.status=1 AND tc.style=1 AND tp.is_upper=1";
                if (!goodsName.equals("")) {
                    sql += " AND tp.name LIKE ?";
                    list.add("%" + goodsName + "%");
                }
                return pageForMapList(sql, nowPage, pageSize, list.toArray());
            } else {
                String sql = "SELECT tp.*,IFNULL ( ( SELECT COUNT(0) FROM t_goods tg WHERE tg.id=tp.id ),0) AS ischeck FROM t_company tc INNER JOIN t_goods tp ON tc.id=tp.company_id  WHERE tp.status=1 AND tc.is_auth=1 AND tc.status=1 AND tc.style=2 AND tp.is_upper=1";
                if (!goodsName.equals("")) {
                    sql += " AND tp.name LIKE ?";
                    list.add("%" + goodsName + "%");
                }
                return pageForMapList(sql, nowPage, pageSize, list.toArray());
            }
        } else {
            if (type != 2) {
                String sql = "SELECT tp.*,IFNULL ( ( SELECT COUNT(0) FROM t_goods tg WHERE tg.id=tp.id AND tp.id IN (" + goodsBusId + ") ),0) AS ischeck FROM t_company tc INNER JOIN t_goods tp ON tc.id=tp.company_id  WHERE tp.status=1 AND tc.is_auth=1 AND tc.status=1 AND tc.style=1 AND tp.is_upper=1";
                if (!goodsName.equals("")) {
                    sql += " AND tp.name LIKE ?";
                    list.add("%" + goodsName + "%");
                }
                return pageForMapList(sql, nowPage, pageSize, list.toArray());
            } else {
                String sql = "SELECT tp.*,IFNULL ( ( SELECT COUNT(0) FROM t_goods tg WHERE tg.id=tp.id AND tp.id IN (" + goodsBusId + ") ),0) AS ischeck FROM t_company tc INNER JOIN t_goods tp ON tc.id=tp.company_id  WHERE tp.status=1 AND tc.is_auth=1 AND tc.status=1 AND tc.style=2 AND tp.is_upper=1";
                if (!goodsName.equals("")) {
                    sql += " AND tp.name LIKE ?";
                    list.add("%" + goodsName + "%");
                }
                return pageForMapList(sql, nowPage, pageSize, list.toArray());
            }
        }
    }

    /**
     * 广告位下的商品
     *
     * @param id
     * @param nowPage
     * @param pageSize
     * @return
     */
    @Override
    public Page queryOutCarouselGoods(Integer id, Integer type, int nowPage, int pageSize) {
        if (type == 0) {
            String sql = "SELECT tp.*,IFNULL ( ( SELECT COUNT(0) FROM t_goods tg WHERE tg.id=tp.id AND tp.id IN (" + id + ") ),0) AS ischeck FROM t_company tc INNER JOIN t_goods tp ON tc.id=tp.company_id  WHERE tp.status=1 AND tc.is_auth=1 AND tc.status=1 AND tc.style=1 AND tp.is_upper=1";
            return pageForMapList(sql, nowPage, pageSize);
        } else {
            String sql = "SELECT tp.*,IFNULL ( ( SELECT COUNT(0) FROM t_goods tg WHERE tg.id=tp.id AND tp.id IN (" + id + ") ),0) AS ischeck FROM t_company tc INNER JOIN t_goods tp ON tc.id=tp.company_id  WHERE tp.status=1 AND tc.is_auth=1 AND tc.status=1 AND tc.style=2 AND tp.is_upper=1";
            return pageForMapList(sql, nowPage, pageSize);
        }
    }

    /**
     * 查询商品
     *
     * @param nowPage
     * @param pageSize
     * @param goodsName
     * @param busName
     * @param type      @return
     * @param business
     */
    @Override
    public Page queryGoods(int nowPage, int pageSize, String goodsName, String busName, Integer type, Integer business) {
        List list = new ArrayList<>();
        String sql = "SELECT tg.*,tc.nick_name AS nickName,ct.name AS typeName,tc.id AS companyId FROM t_goods tg INNER JOIN t_company tc ON tg.company_id = tc.id " +
                "LEFT JOIN t_goods_type ct ON ct.id=tg.goods_type_id WHERE tg.status=1 AND tc.status=1 AND tg.is_upper=1";
        if (StringUtils.isNotBlank(goodsName)) {
            sql += " AND tg.name LIKE ?";
            list.add("%" + goodsName + "%");
        }
        if (StringUtils.isNotBlank(busName)) {
            sql += " AND tc.nick_name LIKE ?";
            list.add("%" + busName + "%");
        }
        if (type > 0) {
            sql += " AND tg.goods_type_id=?";
            list.add(type);
        }
        if (business > 0) {
            sql += " AND tg.company_id=?";
            list.add(business);
        }
        return pageForMapList(sql, nowPage, pageSize, list.toArray());
    }

    /**
     * 商品下架
     *
     * @param id
     * @param isUpper
     * @return
     */
    @Override
    public int updateIsUpper(Integer id, Integer isUpper) {
        String sql = "UPDATE t_goods SET is_upper=? WHERE id=?";
        return update(sql, isUpper, id);
    }

    /**
     * 查询商品交易记录
     *
     * @param id
     * @param nowPage
     * @param pageSize
     * @return
     */
    @Override
    public Page queryGoodsOrder(Integer id, int nowPage, int pageSize) {
        String sql = "SELECT od.*,tc.nick_name AS busName,tu.nick_name AS userName,tr.pay_type AS payType,tr.order_state AS orderState,tr.all_money AS allMoney " +
                "FROM t_goods tg INNER JOIN t_order_detail od ON od.goods_id = tg.id INNER JOIN t_order tr ON tr.order_number=od.order_number " +
                "LEFT JOIN t_company tc ON tc.id=tr.company_id " +
                "LEFT JOIN t_user tu ON tu.id=tr.user_id WHERE od.goods_id=? AND tr.order_state IN (2,3,4,5,6,7,8,9,10,11,12,13)";
        return pageForMapList(sql, nowPage, pageSize, id);
    }

    /**
     * 下架和新添加的商品
     *
     * @param nowPage
     * @param pageSize
     * @param goodsName
     * @param busName
     * @param businessId
     * @param type       @return
     */
    @Override
    public Page queryNewOrOldGoods(int nowPage, int pageSize, String goodsName, String busName, Integer businessId, Integer type) {
        List list = new ArrayList<>();
        String sql = "SELECT tg.*,tc.nick_name AS nickName,ct.name AS typeName,tc.id AS companyId FROM t_goods tg INNER JOIN t_company tc ON tg.company_id = tc.id " +
                "LEFT JOIN t_goods_type ct ON ct.id=tg.goods_type_id WHERE tg.status=1 AND tc.status=1 AND tg.is_upper=2";
        if (StringUtils.isNotBlank(goodsName)) {
            sql += " AND tg.name LIKE ?";
            list.add("%" + goodsName + "%");
        }
        if (StringUtils.isNotBlank(busName)) {
            sql += " AND tc.nick_name LIKE ?";
            list.add("%" + busName + "%");
        }
        if (type > 0) {
            sql += " AND tg.goods_type_id=?";
            list.add(type);
        }
        if (businessId > 0) {
            sql += " AND tg.company_id=?";
            list.add(businessId);
        }
        return pageForMapList(sql, nowPage, pageSize, list.toArray());
    }

    //    /**
//     * 修改参加活动展示
//     *
//     * @param goodsId
//     * @return
//     */
//    @Override
//    public int updateIsJoin(String goodsId) {
//        String sql = "UPDATE t_goods SET is_join=1 WHERE id=?";
//        return update(sql, goodsId);
//    }


    @Override
    public Integer getCompanyGoodsNumber(Integer companyId) {
        String sql = " SELECT COUNT(*) FROM t_goods WHERE company_id=? AND `status`=1 AND is_upper=1";
        return queryForInt(sql, companyId);
    }

    /**
     * 退款后修改商品数量
     *
     * @param goodsId
     * @param stock
     * @return
     */
    @Override
    public int updateGoodsCount(Integer goodsId, Integer stock) {
        String sql = "UPDATE t_goods SET stock=? WHERE id=?";
        return update(sql, stock, goodsId);
    }

    /**
     * 根据商品类别编号与用户购物车查询商品列表
     *
     * @param typeId
     * @param userId
     * @return
     */
    @Override
    public List<Map<String, Object>> findGoodsListByTypeIdAndShopCar(Integer typeId, Integer userId) {
        String sql = " SELECT gd.id,gd.name goodsName,gd.is_bespeak,gd.is_free,gd.unit,gd.is_home_installation,gd.is_no_reason_return," +
                "gd.commercial_activities,gd.box_price,gd.head_pic,gd.price,gd.month_sales,gd.company_id companyId," +
                "gd.discount_number , gd.discount_money , gd.stock ,  IFNULL((SELECT SUM(number) FROM t_shop_car sc " +
                "WHERE sc.goods_id=gd.id AND sc.user_id=?),0) shopCarNum FROM t_goods gd WHERE gd.goods_type_id=? AND gd.is_upper=1 AND gd.status=1 ";
        return queryForMapList(sql, userId, typeId);
    }

    @Override
    public Goods getGoodsById(Integer goodsId) {
        String sql = "SELECT * FROM t_goods WHERE `status`=1 AND id= ?";
        return queryForBean(sql, goodsId);
    }

    /**
     * 查看商家商品分类下是否存在商品
     *
     * @param id
     * @return
     */
    @Override
    public int getGoodsCountByGoodsTypeId(Integer id) {
        String sql = "SELECT * FROM t_goods WHERE `status`=1 AND goods_type_id=?";
        return queryForInt(sql, id);
    }

    /**
     * 商家商品分类下的 商品
     *
     * @param bussId
     * @param goodsTypeId
     * @return
     */
    @Override
    public List<Map<String, Object>> findGoodsList(Integer bussId, Integer goodsTypeId) {
        String sql = "SELECT * FROM t_goods WHERE `status`=1 AND goods_type_id=? AND company_id=? ORDER BY  order_num asc";
        return queryForMapList(sql, goodsTypeId, bussId);
    }

    /**
     * 修改商品
     *
     * @param id
     * @param name
     * @param price
     * @param bagging
     * @param headPic
     * @param images
     * @param unit
     * @param isBespeak
     * @param isHomeInstallation
     * @param isNoReasonReturn
     * @param goodsDetails
     * @param commercialActivities
     * @param isFree
     * @param isInvoice
     * @param bannerImages         @return
     * @param goodsVideo
     * @param goodsVideoImg
     */
    @Override
    public boolean updateGoods(Integer id, String name, Double price, Double bagging, String headPic, String images, String unit, Integer isBespeak, Integer isHomeInstallation, Integer isNoReasonReturn, String goodsDetails, String commercialActivities, Integer isFree, Integer isInvoice, String bannerImages, String goodsVideo, String goodsVideoImg) {
        String sql = "UPDATE t_goods SET `name` = ?, price = ?, box_price = ?, head_pic = ?, images = ?, unit = ?, is_bespeak = ?, is_home_installation = ?,is_no_reason_return=?, goods_detail = ?, commercial_activities = ?, is_free = ?, is_invoice = ?, banner_images = ?,goods_video=?,goods_video_img=? WHERE id = ?";
        return update(sql, name, price, bagging, headPic, images, unit, isBespeak, isHomeInstallation, isNoReasonReturn, goodsDetails, commercialActivities, isFree, isInvoice, bannerImages, goodsVideo, goodsVideoImg, id) > 0;
    }

    /**
     * 删除商品
     */
    @Override
    public int updateGoodsStatus(String id, int status) {
        String sql = "UPDATE `t_goods` SET `status`=? WHERE (`id`=?)";
        return update(sql, status, id);
    }

    /**
     * 修改上下架
     *
     * @param id
     * @param isUpper
     * @return
     */
    @Override
    public boolean updateGoodsIsUpper(Integer id, int isUpper) {
        String sql = " UPDATE {table} SET is_upper=? WHERE id=? ";
        return update(sql, isUpper, id) > 0;
    }

    @Override
    public void updateSaleNum(int goodsId, int number) {
        String sql = "UPDATE t_goods SET month_sales=month_sales+?, all_sale_num=all_sale_num+?  WHERE id=?";
        update(sql, number, number, goodsId);
    }

    /**
     * 修改商品排序
     *
     * @param goodsId
     * @param orderNumber
     * @return
     */
    @Override
    public int updateGoodsOrderNumber(Integer goodsId, Integer orderNumber) {
        String sql = " UPDATE {table} SET order_num=? WHERE id=? ";
        return update(sql, orderNumber, goodsId);
    }

    /**
     * 通过关键字查询商品
     *
     * @param nowPage  当前页
     * @param pageSize 每页条数
     * @param cityId   城市编号
     * @param key      关键字
     * @return
     */
    @Override
    public List<Map<String, Object>> getGoodsByKey(int nowPage, int pageSize, int cityId, String key) {
        String sql = "SELECT g.id AS goodsId, g.company_id, g.head_pic,g.discount_number , g.discount_money , g.stock ,  g.unit, g.commercial_activities , g.is_home_installation, g.is_no_reason_return, g.is_join, g.price, g.month_sales AS saleNum , c.city_id, g.`name` AS goodsName, c.buss_name AS companyName FROM t_goods g INNER JOIN t_bussiness c ON c.id = g.company_id AND c.state=1 WHERE g.`status` = 1 AND g.is_upper = 1 AND c.city_id = ?";
        if (!key.equals("")) {
            sql += "AND g.`name` like '%" + key + "%'";
        }
        return listForMapList(sql, nowPage, pageSize, cityId);
    }

    /**
     * 查询商品详细
     *
     * @param goodsId
     * @param nowPage
     * @param pageSize
     * @return
     */
    @Override
    public Page queryGoodsDetails(Integer goodsId, int nowPage, int pageSize) {
        String sql = "SELECT gs.spec_name AS specName,sv.value_name AS valueName,sv.price FROM " +
                "t_goods tg INNER JOIN t_goods_spec gs ON tg.id=gs.goods_id INNER JOIN t_goods_spec_value sv ON sv.spec_id=gs.id  " +
                "WHERE tg.id=? AND tg.status=1";
        return pageForMapList(sql, nowPage, pageSize, goodsId);
    }

    /**
     * 广告位下商品
     *
     * @param goodsId
     * @param nowPage
     * @param pageSize
     * @return
     */
    @Override
    public Page queryCarouselGoods(String goodsId, int nowPage, int pageSize) {
        String sql = "SELECT * FROM t_goods WHERE id IN (" + goodsId + ") AND status=1";
        return pageForMapList(sql, nowPage, pageSize);
    }

    /**
     * 修改商品城市编号
     *
     * @param minCityId
     * @param id
     */
    @Override
    public void updateGoodsCity(int minCityId, Integer id) {
        String sql = "UPDATE t_goods SET city_id=? WHERE company_id=?";
        update(sql, minCityId, id);
    }

    @Override
    public List<Map<String, Object>> getSpecialGoods(int cityId, int nowPage, int pageSize,
                                                     String uLng, String uLat, int distance) {
        String sql = "SELECT goodsList.* FROM (SELECT g.id AS goodsId, g.company_id busId,g.is_home_installation," +
                " g.head_pic, g.price, g.commercial_activities, g.month_sales saleNum," +
                " g.discount_number , g.discount_money , g.stock ,  g.city_id,g.unit," +
                " g.`name` goodsName, IFNULL(sc.number, 0) AS goodsNumber , g.is_invoice, g.is_no_reason_return," +
                " g.is_bespeak, g.is_free, g.is_special,ROUND(6378.138 * 2 * ASIN(SQRT(POW(SIN((" + uLat +
                " * PI() / 180 - b.lat * PI() / 180) / 2), 2) + COS(" + uLat + " * PI() / 180) * COS(b.lat * PI() / 180)" +
                " * POW(SIN((" + uLng + " * PI() / 180 - b.lng * PI() / 180) / 2), 2))) * 1000) AS juli " +
                "FROM t_goods g LEFT JOIN t_shop_car sc ON sc.goods_id = g.id AND sc.user_id=0 " +
                " LEFT JOIN t_bussiness b ON g.company_id=b.id  WHERE `status` = 1 AND is_special = 1 AND is_upper = 1 " +
                "AND b.state=1 AND TO_SECONDS(NOW()) <= TO_SECONDS(expiration_time) ) goodsList WHERE goodsList.juli <= ? ";
        // AND g.city_id=?   cityId,
        sql += "   ORDER BY goodsList.juli ASC,goodsList.busId";
        return listForMapList(sql, nowPage, pageSize, distance);
    }

    @Override
    public List<Map<String, Object>> getGoodsByBussAndTypeID(int nowPage, int pageSize, int typeId, int userId) {
        String sql = "";
        return listForMapList(sql, nowPage, pageSize, userId, typeId);
    }

    /**
     * 是否还有在线的商品
     *
     * @param busId
     * @return
     */
    @Override
    public Map<String, Object> getGoodsUpperNum(Integer busId) {
        String sql = "SELECT COUNT(*) AS num FROM t_goods WHERE company_id = ? AND status=1";
        return queryForMap(sql, busId);
    }

    /**
     * 查询商家的全部商品
     *
     * @param bussId   商家编号
     * @param nowPage  当前页
     * @param pageSize @return
     * @return
     */
    @Override
    public List<Map<String, Object>> findAllGoodsList(Integer bussId, Integer nowPage, int pageSize) {
        String sql = "SELECT * FROM t_goods WHERE `status`=1 and is_upper=1 AND company_id=? ORDER BY  expiration_time DESC,created DESC";
        return listForMapList(sql, nowPage, pageSize, bussId);
    }

    /**
     * 修改商品为特价商品
     *
     * @param goodsId
     * @param expirationTime
     * @param goodsDiscountPrice
     * @param discountNumber     @return
     */
    @Override
    public boolean updateIsSpecial(Integer goodsId, Date expirationTime, BigDecimal goodsDiscountPrice, Integer discountNumber) {
        String sql = "UPDATE `t_goods` SET `expiration_time`=?, `is_special`='1',`stock`=?, `discount_number`=?, `discount_money`=? WHERE id=?";
        return update(sql, expirationTime, discountNumber, discountNumber, goodsDiscountPrice, goodsId) > 0;
    }

    @Override
    public int getGoodsCountBYBusId(Integer bussId) {
        String sql = "SELECT count(0) FROM `t_goods` WHERE `status` = '1' AND `company_id` = ? ";
        return queryForInt(sql, bussId);
    }

    /**
     * @param nowPage
     * @param pageSize
     * @param goodsName
     * @param isSpecial
     * @param bussName
     * @param type
     * @return
     */
    @Override
    public Page findGoods(int nowPage, int pageSize, String goodsName, int isSpecial, String bussName, Integer type) {
        List<Object> params = new ArrayList<Object>();
        String sql = "SELECT a.*,b.buss_name AS bName,bt.name AS typeName,b.id AS bId FROM t_bussiness b " +
                "INNER JOIN t_goods a ON a.company_id=b.id INNER JOIN  t_goods_type bt ON bt.id=a.goods_type_id " +
                "WHERE a.status=1 AND a.is_upper=1 AND b.state=1 ";
        if (StringUtils.isNotBlank(goodsName)) {
            sql += " and a.name like ?";
            params.add("%" + goodsName + "%");
        }

        if (isSpecial > 0) {//特价商品
            sql += " and a.is_special=? ";
            params.add(isSpecial);
        }
        if (isSpecial == 0) {
            sql += " and a.is_special IN (0,2)";
        }
        if (type > -1) {
            sql += " and a.goods_type_id=?";
            params.add(type);
        }
        if (StringUtils.isNotBlank(bussName)) {
            sql += " and b.buss_name like ?";
            params.add("%" + bussName + "%");
        }
        sql += " ORDER BY a.created DESC";
        return pageForMapList(sql, nowPage, pageSize, params.toArray());
    }

    /**
     * @param id
     * @param isSpecial
     * @return
     */
    @Override
    public int noUpper(int id, Integer isSpecial) {
        if (isSpecial == 0) {
            String sql = "update t_goods a set a.is_upper=0 where id=" + id + "";
            return update(sql);
        } else {
            String sql = "update t_goods  set is_special=?,expiration_time=NULL where id=" + id + "";
            return update(sql, isSpecial);
        }
    }

    /**
     * @param nowPage
     * @param pageSize
     * @param goodsName
     * @param busName
     * @return
     */
    @Override
    public Page noUpperList(int nowPage, int pageSize, String goodsName, String busName) {
        List<Object> params = new ArrayList<Object>();
        String sql = "SELECT a.*,b.buss_name AS bName FROM t_goods a INNER JOIN t_bussiness b ON a.company_id=b.id WHERE a.status=1 AND a.is_upper=0 AND b.state=1";
        if (StringUtils.isNotBlank(goodsName)) {
            sql += " and a.name like ?";
            params.add("%" + goodsName + "%");
        }
        if (StringUtils.isNotBlank(busName)) {
            sql += " and b.buss_name like ?";
            params.add("%" + busName + "%");
        }
        return pageForMapList(sql, nowPage, pageSize, params.toArray());
    }

    /**
     * @param id
     * @return
     */
    @Override
    public int yesUpper(int id) {
        String sql = "update t_goods a set a.is_upper=1 where id=?";
        return update(sql, id);
    }

    /**
     * @param id
     * @return
     */
    @Override
    public int deleteGoodsById(int id) {
        String sql = "update t_goods a set a.status=0 where id=" + id + "";
        return update(sql);
    }

    /**
     * @param nowPage
     * @param pageSize
     * @param goodsName
     * @return
     */
    @Override
    public Page queryDayOffer(int nowPage, int pageSize, String goodsName) {
        String sql = "SELECT ch.*,tg.name FROM t_goods tg INNER JOIN t_company_panyment_history ch ON tg.id=ch.good_id WHERE ch.payment_type=3";
        if (!goodsName.equals("")) {
            sql += " AND tg.`name` LIKE '%" + goodsName + "%' ";
        }
        sql += " ORDER BY ch.created_time DESC";
        return pageForMapList(sql, nowPage, pageSize);
    }

    /**
     * @param id
     * @param goodsName
     * @param nowPage
     * @param pageSize  @return
     */
    @Override
    public Page businessGoods(Integer id, String goodsName, int nowPage, int pageSize) {
        String sql = "SELECT * FROM t_goods WHERE company_id=? AND status=1";
        if (!goodsName.equals("")) {
            sql += " AND name LIKE ?";
            return pageForMapList(sql, nowPage, pageSize, id, "%" + goodsName + "%");
        }
        return pageForMapList(sql, nowPage, pageSize, id);
    }

    @Override
    public List<Map<String, Object>> findEquipmentList(int companyTypeId, int nowPage, int pageSize, int userId) {
        String sql = "SELECT gd.id, gd.NAME AS goodsName, gd.is_bespeak, gd.is_free, gd.unit , gd.is_home_installation, gd.is_no_reason_return, gd.commercial_activities, gd.box_price, gd.head_pic , gd.price, gd.month_sales, gd.company_id AS companyId , IFNULL(( SELECT SUM(number) FROM t_shop_car sc WHERE sc.goods_id = gd.id AND sc.user_id = ? ), 0) AS shopCarNum FROM t_goods gd INNER JOIN t_bussiness b ON b.id = gd.company_id WHERE (b.buss_type_id = ? AND gd.is_upper = 1 AND gd.STATUS = 1)";
        return listForMapList(sql, nowPage, pageSize, userId, companyTypeId);
    }
}